import os, sys

sys.path.append(os.getcwd())
import pandas
import numpy
import datetime
from src.utils.db_processor import mysql_prediction_processor, presto_processor
from src.utils.feishu_message import feishu_messager

# 显示所有列
pandas.set_option('display.max_columns', None)
# 显示所有行presto_processor
pandas.set_option('display.max_rows', None)
# 设置value的显示长度为100，默认为50
pandas.set_option('max_colwidth', 100)



def get_popular_product(model_date):
    '''
    获取热门型号
    近7天 销售量大于20的型号
    '''
    sql = '''
    select
    product_id
    from
    (
        select
        psdp.product_id,count(1) as sale_out_num
        from 
        dw.dw_platform_settle_document_product psdp
        JOIN dim.dim_product_sku dps ON dps.product_sku_id = psdp.product_combine_sku_id
        join dw.dw_source_product_no_front_sku_level sku2level on psdp.product_no =sku2level.product_no 
		and date(sku2level.partition_date)= psdp.quotation_document_begin_date
        JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
        left join dw.dw_centre_document_product_out_info cdp_o on psdp.product_no = cdp_o.product_no AND psdp.settle_document_no = cdp_o.document_serial_no
        and cdp_o.document_create_date>=cast('{0}' as date) and cdp_o.document_create_date<cast('{1}' as date) 
        where settle_document_create_date>=date '{0}' and settle_document_create_date<date '{1}'
        and ((psdp.return_dt is null) and (psdp.cancel_dt is null))
        AND pdp.quotation_document_type_id = 10
        and psdp.start_price>0
        AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
        and psdp.product_source_id in  (101, 103)
        and dps.product_category_id=1
        and (cdp_o.sale_out_cnt is null or cdp_o.sale_out_cnt=1)
        group by psdp.product_id
    )aa where sale_out_num>20
    '''
    model_date_datetime = datetime.datetime.strptime(model_date, "%Y-%m-%d")
    start_date_datetime = model_date_datetime - datetime.timedelta(days=7)
    start_date = start_date_datetime.strftime('%Y-%m-%d')
    popular_product_data = presto_processor.load_sql(sql.format(start_date, model_date))

    popular_product_id = list(popular_product_data['product_id'])

    return popular_product_id



def get_real_price_data(model_date, popular_product_id):
    model_date_datetime = datetime.datetime.strptime(model_date, "%Y-%m-%d")
    start_date_datetime = model_date_datetime - datetime.timedelta(days=4)
    start_date = start_date_datetime.strftime('%Y-%m-%d')
    # print('start_date :', start_date)
    # print('model_date :', model_date)

    history_data_sql = '''
    select
    psdp.product_no,psdp.product_id,psdp.product_combine_sku_id,psdp.product_combine_sku_name,
    dps.product_brand_id,
    sku2level.secondary_level_id, --二级level,c2b使用
    sku2level.secondary_sku_id, --二级skuid,c2b使用
    cast(psdp.settle_amount_num as int) AS item_quotation_price_num
    from 
    dw.dw_platform_settle_document_product psdp
    JOIN dim.dim_product_sku dps ON dps.product_sku_id = psdp.product_combine_sku_id
    join dw.dw_source_product_no_front_sku_level sku2level on psdp.product_no =sku2level.product_no and date(sku2level.partition_date)= psdp.quotation_document_begin_date
    JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
    left join dw.dw_centre_document_product_out_info cdp_o on psdp.product_no = cdp_o.product_no AND psdp.settle_document_no = cdp_o.document_serial_no
    and cdp_o.document_create_date>=cast('{0}' as date) and cdp_o.document_create_date<cast('{1}' as date) 
    where settle_document_create_date>=date '{0}' and settle_document_create_date<date '{1}'
    and ((psdp.return_dt is null) and (psdp.cancel_dt is null))
    AND pdp.quotation_document_type_id = 10
    and psdp.start_price>0
    AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
    and psdp.product_source_id in  (101, 103)
    and sku2level.secondary_level_id in (404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,2742,2744,2745,500,501)
    and psdp.product_id in ({2})
    and dps.product_category_id=1
    and (cdp_o.sale_out_cnt is null or cdp_o.sale_out_cnt=1)
    '''
    popular_product_id = [str(i) for i in popular_product_id]
    popular_product_id_str = ','.join(popular_product_id)


    history_real_data = presto_processor.load_sql(history_data_sql.format(start_date, model_date, popular_product_id_str))
    print(history_real_data.head())

    history_real_data['secondary_sku_id'] = history_real_data['secondary_sku_id'].astype(str)
    sku_id_lis = list(set(history_real_data['secondary_sku_id']))
    sku_id_str = ','.join(sku_id_lis)

    predict_data_sql = '''
    select
    product_sku_key as secondary_sku_id,
    product_level_key as secondary_level_id,
    forecast_reference_price,
    product_key as product_id,
    product_brand_id
    from
    price_prediction
    where date='{0}' and product_sku_key in ({1})
    and product_category_id=1
    '''

    # predict_data_sql = '''
    # select
    # product_sku_key as secondary_sku_id,
    # product_level_key as secondary_level_id,
    # forecast_reference_price,
    # product_key as product_id,
    # product_brand_id
    # from
    # ods.ods_price_prediction_price_prediction
    # where predict_date='{0}' and product_sku_key in ({1})
    # and product_category_id=1
    # '''

    real_price_data = mysql_prediction_processor.load_sql(predict_data_sql.format(model_date, sku_id_str))
    # real_price_data = presto_processor.load_sql(predict_data_sql.format(model_date, sku_id_str))
    # real_price_data['product_no'] = real_price_data['product_no'].astype(str) + '_'

    return real_price_data, history_real_data


def delete_extreme_data():
    '''
    删除极端数据
    :return:
    '''
    pass


def price_level(x):
    if x < 1000:
        price_level = '1000'
    elif x >= 1000 and x < 3000:
        price_level = '3000'
    elif x >= 3000 and x < 5000:
        price_level = '5000'
    else:
        price_level = '5000+'

    return price_level


def get_price_level(data, price_feature1, price_feature2):
    price_level_data = data.groupby(['price_level'])[price_feature1, price_feature2].mean()
    price_level_data = price_level_data.reset_index()
    # price_level_data.rename(columns={price_feature: 'real_avg_price'})

    # print('brand_price_level_data :',brand_price_level_data)
    return price_level_data


def sku_level_grouped_price(data, price_feature):
    data['product_brand_id'] = data['product_brand_id'].astype(str)
    data['secondary_sku_id'] = data['secondary_sku_id'].astype(str)
    data['secondary_level_id'] = data['secondary_level_id'].astype(str)
    data['product_id'] = data['product_id'].astype(str)
    data['sku_level_brand_product_id'] = data['secondary_sku_id'] + '_' + data['secondary_level_id'] + '_' + data[
        'product_brand_id'] + '_' + data['product_id']
    sku_level_brand_data = data.groupby(['sku_level_brand_product_id'])[price_feature].mean()
    sku_level_brand_data = sku_level_brand_data.reset_index()
    return sku_level_brand_data


def get_brand_price_level(data, price_feature1, price_feature2):
    # data['price_level'] = data[price_feature].apply(price_level)
    brand_price_level_data = data.groupby(['brand_id', 'price_level'])[price_feature1, price_feature2].mean()
    brand_price_level_data = brand_price_level_data.reset_index()
    # brand_price_level_data.rename(columns={price_feature: 'real_avg_price'})
    # print('brand_price_level_data :',brand_price_level_data)
    return brand_price_level_data


def get_product_grouped_price(data, price_feature1, price_feature2):
    # data['price_level'] = data[price_feature].apply(price_level)
    brand_price_level_data = data.groupby(['product_id'])[price_feature1, price_feature2].mean()
    brand_price_level_data = brand_price_level_data.reset_index()
    # brand_price_level_data.rename(columns={price_feature: 'real_avg_price'})
    # print('brand_price_level_data :',brand_price_level_data)
    return brand_price_level_data


def get_special_sku_id(data, sku_id_col_name):
    '''
    CPU in(['其它处理器','其他处理器','非酷睿 i 系列']))
    GPU.in(['其它显卡','其他显卡']))
    型号名 可开机笔记本
    处理其他属性都有，无机械硬盘无ssd的情况
    无属性信息的产品
    '''
    sku_property_sql = '''
        select
        product_sku_id,
        product_name,
        property_name,
        property_value_name
        from
        dim.dim_product_sku_sub_product
        where 
        product_sku_id in ({})
    '''
    data[sku_id_col_name + '_str'] = data[sku_id_col_name].astype(str)
    secondary_sku_ids = list(set(data[sku_id_col_name + '_str']))
    secondary_sku_ids = ','.join(secondary_sku_ids)
    # print(secondary_sku_ids)
    sku_property_data = presto_processor.load_sql(sku_property_sql.format(secondary_sku_ids))  # 获取笔记本属性值
    special_sku_ids = []
    special_sku_id_1 = list(set(sku_property_data[sku_property_data['product_name'] == '可开机笔记本']['product_sku_id']))
    special_sku_id_2 = list(set(sku_property_data[sku_property_data['property_value_name'].isin(
        ['其它处理器', '其他处理器', '非酷睿 i 系列', '其它显卡', '其他显卡'])]['product_sku_id']))
    special_sku_id_3 = list(set(data['sku_id']) - set(sku_property_data['product_sku_id']))  # 无属性sku
    special_sku_id_4 = list(set(sku_property_data['product_sku_id']) - set(
        sku_property_data[sku_property_data['property_name'].isin(['固态硬盘', '机械硬盘'])][
            'product_sku_id']))  # 处理其他属性都有，无机械硬盘无ssd的情况
    special_sku_ids.extend(special_sku_id_1)
    special_sku_ids.extend(special_sku_id_2)
    special_sku_ids.extend(special_sku_id_3)
    special_sku_ids.extend(special_sku_id_4)
    special_sku_ids = list(set(special_sku_ids))
    print('special_sku_id_1 :', special_sku_id_1)
    print('special_sku_id_2 :', special_sku_id_2)
    print('special_sku_id_3 :', special_sku_id_3)
    print('special_sku_id_4 :', special_sku_id_4)
    return special_sku_ids


def get_price_remove_outlier(price_rate, price_diff):
    '''
    价格去除异常值
    '''
    # print('sku_id :',sku_id)
    # print('special_sku_ids :',special_sku_ids)
    flag = 0
    if ((price_rate < -0.3) and (price_diff > 100)) or ((price_rate > 0.3) and (price_diff > 100)):
        flag = 1

    return flag


def get_eval_data(data):
    data['old_mape'] = numpy.abs(data['真实售价'] - data['预测价格']) / data['真实售价']
    data['new_mape'] = numpy.abs(data['真实售价'] - data['预测价跟价价格']) / data['真实售价']

    data['old_absolute_num'] = numpy.abs(data['真实售价'] - data['预测价格'])
    data['new_absolute_num'] = numpy.abs(data['真实售价'] - data['预测价跟价价格'])

    all_old_mape = numpy.mean(data['old_mape'])
    all_new_mape = numpy.mean(data['new_mape'])

    old_relative_deviation = str(
        numpy.round(((numpy.sum(data['真实售价']) - numpy.sum(data['预测价格'])) / numpy.sum(data['真实售价'])) * 100, 2)) + '%'
    new_relative_deviation = str(
        numpy.round(((numpy.sum(data['真实售价']) - numpy.sum(data['预测价跟价价格'])) / numpy.sum(data['真实售价'])) * 100, 2)) + '%'

    old_absolute_deviation = str(
        numpy.round(numpy.sum(data['old_absolute_num']) / numpy.sum(data['真实售价']) * 100, 2)) + '%'
    new_absolute_deviation = str(
        numpy.round(numpy.sum(data['new_absolute_num']) / numpy.sum(data['真实售价']) * 100, 2)) + '%'

    # print('all_old_mape,old_relative_deviation,old_absolute_deviation :',all_old_mape,old_relative_deviation,old_absolute_deviation)
    # print('all_new_mape,new_relative_deviation,new_absolute_deviation :',all_new_mape,new_relative_deviation,new_absolute_deviation)
    return all_old_mape, old_relative_deviation, old_absolute_deviation, all_new_mape, new_relative_deviation, new_absolute_deviation


def rate_cut(rate, threshold=0.03):
    if rate > 0.02:
        return 0.02
    elif rate < -threshold:
        return -threshold
    return rate


def split_sku_level_brand_product(x):
    sku_level_brand = x.split('_')
    sku_id = sku_level_brand[0]
    level_id = sku_level_brand[1]
    brand_id = sku_level_brand[2]
    product_id = sku_level_brand[3]
    return sku_id, level_id, brand_id, product_id


def update_price_by_product(real_price_data, history_real_data):
    '''
    获取最近3天真实数据
    型号 跟价
    product_id, sku_id, level, avg_price
    '''
    # sku_level级别聚合
    sku_level_brand_history_data = sku_level_grouped_price(history_real_data, 'item_quotation_price_num')
    sku_level_brand_history_data.rename(columns={'item_quotation_price_num': 'history_avg_price'}, inplace=True)

    sku_level_brand_real_data = sku_level_grouped_price(real_price_data, 'forecast_reference_price')
    sku_level_brand_real_data.rename(columns={'forecast_reference_price': 'today_predict_price'}, inplace=True)

    data = pandas.merge(sku_level_brand_real_data, sku_level_brand_history_data, how='inner',on=['sku_level_brand_product_id'])

    data['sku_id'], data['level_id'], data['brand_id'], data['product_id'] = zip(
        *data['sku_level_brand_product_id'].apply(split_sku_level_brand_product))  # 防止将正常重复的item_name给去重了
    data['sku_id'] = data['sku_id'].astype(int)
    data['level_id'] = data['level_id'].astype(int)
    data['brand_id'] = data['brand_id'].astype(int)
    data['product_id'] = data['product_id'].astype(int)

    data['price_rate'] = (data['history_avg_price'] - data['today_predict_price']) / data['history_avg_price']
    data['price_diff'] = numpy.abs(data['history_avg_price'] - data['today_predict_price'])

    # print(len(set(data['product_id'])))
    # 将特殊sku_id加入到不调价范围内
    # outlier_sku_id_lis = get_special_sku_id(data, 'sku_id')
    # print('outlier_sku_id_lis :', outlier_sku_id_lis)
    outlier_sku_id_lis = []

    data['is_outlier'] = data.apply(lambda x: get_price_remove_outlier(x['price_rate'], x['price_diff']), axis=1)
    data['sku_level'] = data['sku_id'].astype(str)+'_'+data['level_id'].astype(str)
    outlier_sku_level_lis = list(set(data[(data['is_outlier'] == 1)]['sku_level']))  # 异常sku


    '''
    异常sku_level和没有在过去三天出现过的sku_level预测价格不调整
    '''

    # data_no_outlier = data[(data['is_outlier']==0)] #非异常值
    data_no_outlier = data[~(data['sku_level'].isin(outlier_sku_level_lis))]  # 非异常值
    # data_no_outlier = data_no_outlier[~(data_no_outlier['sku_id'].isin(outlier_sku_id_lis))]  # 非异常值

    # print('data_no_outlier :',data_no_outlier.head())

    # data_no_outlier_other_brand = data_no_outlier[~data_no_outlier['brand_id'].isin(top10_brand_id)]
    # data_no_outlier_top_brand = data_no_outlier[data_no_outlier['brand_id'].isin(top10_brand_id)]

    # sku_level偏差小时不跟价
    # no_change_price_sku_level = list(set(
    #     data_no_outlier[(data_no_outlier['price_rate'] <= 0.01) & (data_no_outlier['price_rate'] >= -0.01)][
    #         'sku_level_brand_product_id']))

    # data_no_outlier_top_brand = data_no_outlier

    product_size_data = data_no_outlier.groupby(['product_id']).size()
    product_size_data = product_size_data.reset_index()
    product_size_data.columns = ['product_id', 'product_size']
    data_no_outlier = pandas.merge(data_no_outlier, product_size_data, how='left',
                                             on=['product_id'])
    data_no_outlier = data_no_outlier[data_no_outlier['product_size'] > 1]


    product_grouped_price_rate = get_product_grouped_price(data_no_outlier, 'history_avg_price','today_predict_price')

    product_grouped_price_rate['price_rate'] = (product_grouped_price_rate['history_avg_price'] - product_grouped_price_rate['today_predict_price']) / product_grouped_price_rate['history_avg_price']
    product_grouped_price_rate['real_price_rate'] = product_grouped_price_rate['price_rate'].apply(rate_cut)
    product_grouped_price_rate = product_grouped_price_rate[['product_id', 'real_price_rate']]
    product_grouped_price_rate['product_id'] = product_grouped_price_rate['product_id'].astype(int)
    # data_no_outlier_top_brand_brand_price_level.rename(columns={'price_level': 'brand_price_level'},inplace=True)

    '''
    1.预测数据 价位段设置
    2 假设是否正确
    3 sku会不会产生属性倒挂

    开始对预测数据进行跟价处理
    2.top品牌数据按照 品牌+价位段 跟价
    3.其他品牌数据按照 价位段 跟价
    1.异常skulevel 和在前三天没有售出记录的 品牌+价位段 不跟价
    '''
    # print('real_price_data :',real_price_data.head())
    real_price_data['product_brand_id'] = real_price_data['product_brand_id'].astype(int)
    real_price_data['secondary_sku_id'] = real_price_data['secondary_sku_id'].astype(int)
    real_price_data['product_id'] = real_price_data['product_id'].astype(int)
    # real_price_data['price_level'] = real_price_data['forecast_reference_price'].apply(price_level)
    # real_price_data_top_brand =  real_price_data[real_price_data['product_brand_id'].isin(top10_brand_id)]
    # real_price_data_other_brand =  real_price_data[~real_price_data['product_brand_id'].isin(top10_brand_id)]
    # real_price_data_top_brand = real_price_data

    real_price_data = pandas.merge(real_price_data, product_grouped_price_rate,how='left', left_on=['product_id'], right_on=['product_id'])
    real_price_data['real_price_rate'] = real_price_data['real_price_rate'].fillna(0)
    # real_price_data_top_brand['is_top_brand'] = 1

    # print('real_price_data_top_brand :',real_price_data_top_brand.head())

    # real_price_data_other_brand = pandas.merge(real_price_data_other_brand, data_no_outlier_other_brand_price_level, how='left', left_on=['price_level'],right_on=['brand_price_level'])
    # real_price_data_other_brand['real_price_rate'] = real_price_data_other_brand['real_price_rate'].fillna(0)
    # real_price_data_other_brand['is_top_brand'] = 0

    # print('real_price_data_other_brand :',real_price_data_other_brand.head())
    # result_data = real_price_data_top_brand
    # result_data = pandas.concat([real_price_data_top_brand, real_price_data_other_brand])
    # print('result_data :',result_data.head())
    # print('real_price_data :',real_price_data.shape)
    # print('result_data :',result_data.shape)

    # real_price_data.loc[(real_price_data['secondary_sku_id'].isin(outlier_sku_id_lis)), 'real_price_rate'] = 0
    # real_price_data.loc[(real_price_data['sku_level_brand_product_id'].isin(no_change_price_sku_level)), 'real_price_rate'] = 0
    real_price_data['sku_level'] = real_price_data['secondary_sku_id'].astype(str)+'_'+real_price_data['secondary_level_id'].astype(str)
    real_price_data.loc[(real_price_data['sku_level'].isin(outlier_sku_level_lis)), 'real_price_rate'] = 0



    # print('result_data :',result_data.head())
    # print('real_price_data :',real_price_data.shape)
    # print('result_data :',result_data.shape)

    real_price_data['new_rate'] = 1 - real_price_data['real_price_rate']
    real_price_data['new_price'] = real_price_data['forecast_reference_price'] / real_price_data['new_rate']

    print('real_price_data', real_price_data.head())
    del real_price_data['new_rate']
    real_price_data.rename(
        columns={'forecast_reference_price': '预测价格', 'new_price': '预测价跟价价格'},
        inplace=True)
    # result_data = result_data[['真实售价','预测价格','预测价跟价价格']]
    return real_price_data


def insert_mysql_data(result_data):
    insert_sql = """
    INSERT INTO price_prediction_price_follow_late(date, product_sku_key, product_level_key, product_category_id, forecast_reference_price, follow_price)
    VALUES(%s, %s, %s, %s, %s, %s)
    """
    mysql_prediction_processor.execute_insert_sql(insert_sql,
                                         result_data[
                                             ['data_date', 'secondary_sku_id', 'secondary_level_id','product_category_id','预测价格', '预测价跟价价格']
                                         ].to_records(index=False).tolist())
    print('insert data down')

def update_mysql_data(data_date):
    sql1 = '''
    UPDATE price_prediction
    set predict_origin_mse=POLY_pred_price
    where product_category_id=1
    '''
    mysql_prediction_processor.execute_sql(sql1)

    sql2 = '''
    UPDATE price_prediction a
    JOIN price_prediction_price_follow_late b ON a.product_sku_key = b.product_sku_key and a.product_level_key = b.product_level_key
    SET a.predict_origin_mse = b.follow_price
    where a.product_category_id=1 and b.date='{0}'
    '''
    mysql_prediction_processor.execute_sql(sql2.format(data_date))



def main():
    try:
        data_date = (datetime.datetime.now()+ datetime.timedelta(days=1)).strftime('%Y-%m-%d')
        popular_product_id = get_popular_product(data_date)  # 热门型号
        real_price_data, history_real_data = get_real_price_data(data_date, popular_product_id)
        result_data = update_price_by_product(real_price_data, history_real_data)

        result_data = result_data[result_data['real_price_rate']!=0]
        result_data['data_date'] = data_date
        result_data['product_category_id'] = 1
        insert_mysql_data(result_data)
        update_mysql_data(data_date)
    except Exception as e:
        feishu_messager.send_message('手机跟价测试失败：{}'.format(e))
        raise TypeError('手机跟价测试失败:') from e
        exit(1)

if __name__ == '__main__':
    main()







